<?php
//1.创建连接
$mysqlCon=@new mysqli(dburl, dbuname, dbupwd, dbname, dbport)
    or $_SESSION['$errorTipArr'][]='数据库连接失败';

//2.设置字符集
mysqli_set_charset($mysqlCon,'utf-8');

//3.创建SQL语句
//忘记密码查询用户名与ID
const queryIsByIDName='SELECT * FROM userinfolist WHERE uid = ? AND uname = ?';
//查询除管理员外的所有用户数量
const queryAllUser='SELECT * FROM userinfolist WHERE uiden != 0';
//查询七天内登录人数
const queryUserLastLoginNumsSQL="SELECT * FROM userinfolist WHERE uiden != 0 AND  date_sub(curdate(), interval 7 day) <= date(ulastlogintime);";
//查询除登录者外的所有用户(分页)
const queryAllexLogin='SELECT * FROM userinfolist  WHERE uid != ? AND uiden != ? AND uname LIKE ? ORDER BY uid ASC limit ? , 3 ';
//查询除登录者外的所有用户(记录数)
const queryAllexLoginNum='SELECT * FROM userinfolist WHERE uid != ? AND uiden != ? AND uname LIKE ? ';

//查询邮箱里的消息(分页)
const queryBrowseAll='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate !=2  AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询邮箱里的消息(记录数)
const queryBrowseAllNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate !=2  AND m.messtitle LIKE ? ';


//查询邮箱里未读的消息(页头使用)
const queryBrowseLNewButUnRead='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender  WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate !=2 AND ISNULL(m.gettime) ORDER BY m.sendtime DESC';
//查询邮箱里未读的消息(分页)
const queryBrowseAllButUnRead='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate !=2 AND ISNULL(m.gettime)  AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询邮箱里未读的消息(记录数)
const queryBrowseAllButUnReadNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate !=2 AND ISNULL(m.gettime)  AND m.messtitle LIKE ? ';

//查询邮箱里已读的消息(分页)
const queryBrowseAllButRead='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate !=2 AND m.gettime!=\'\'  AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询邮箱里已读的消息(记录数)
const queryBrowseAllButReadNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate !=2 AND m.gettime!=\'\'  AND m.messtitle LIKE ? ';

//查询邮箱里的未读的消息(不分页下的记录数)
const queryBrowseUnreadNum='SELECT * FROM usermessage  WHERE messgetter= ? AND sendtime !=\'\' AND ISNULL(gettime) AND messstate !=2 ';

//查询收藏的消息(分页)
const queryBrowseSart='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate = 1  AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询收藏的消息(记录数)
const queryBrowseSartNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\'  AND m.messstate = 1  AND m.messtitle LIKE ? ';

//查询收藏未读的消息(分页)
const queryBrowseSartButUnRead='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate = 1 AND ISNULL(m.gettime) AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询收藏未读的消息(记录数)
const queryBrowseSartButUnReadNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\'  AND m.messstate = 1 AND ISNULL(m.gettime) AND m.messtitle LIKE ? ';

//查询收藏已读的消息(分页)
const queryBrowseSartButRead='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate = 1 AND m.gettime!=\'\' AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询收藏已读的消息(记录数)
const queryBrowseSartButReadNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\'  AND m.messstate = 1 AND m.gettime!=\'\' AND m.messtitle LIKE ? ';

//查询草稿里的消息(分页)
const queryBrowseSave='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messgetter WHERE m.messsender= ? AND ISNULL(m.sendtime) AND m.messtitle LIKE ? ORDER BY m.mid DESC limit ? , 3 ';
//查询草稿里的消息(记录数)
const queryBrowseSaveNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messgetter WHERE m.messsender= ? AND ISNULL(m.sendtime) AND messtitle LIKE ?';

//查询已发送的消息(分页)
const queryBrowseSend='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messgetter WHERE m.messsender= ? AND m.sendtime !=\'\' AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询已发送的消息(记录数)
const queryBrowseSendNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messgetter WHERE m.messsender= ? AND m.sendtime !=\'\' AND messtitle LIKE ?';

//查询回收站的消息(分页)
const queryBrowseTrash='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate = 2  AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询回收站的消息(记录数)
const queryBrowseTrashNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\'  AND m.messstate = 2  AND m.messtitle LIKE ? ';
//查询回收站的消息(不分页下的记录数)
const queryBrowseTrashAllNum='SELECT * FROM usermessage  WHERE messgetter = ? AND sendtime !=\'\' AND messstate = 2  ';

//查询回收站未读的消息(分页)
const queryBrowseTrashButUnRead='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate = 2 AND ISNULL(m.gettime) AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询回收站未读的消息(记录数)
const queryBrowseTrashButUnReadNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\'  AND m.messstate = 2 AND ISNULL(m.gettime) AND m.messtitle LIKE ? ';

//查询回收站已读的消息(分页)
const queryBrowseTrashButRead='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\' AND m.messstate = 2 AND m.gettime!=\'\' AND m.messtitle LIKE ? ORDER BY m.gettime ASC , m.sendtime DESC limit ? , 3 ';
//查询回收站已读的消息(记录数)
const queryBrowseTrasButReadNum='SELECT m.* , u.uname FROM usermessage m left JOIN userinfolist u on u.uid = m.messsender WHERE m.messgetter= ? AND m.sendtime !=\'\'  AND m.messstate = 2 AND m.gettime!=\'\' AND m.messtitle LIKE ? ';

//根据MID查询特定消息
const queryMessByMId='SELECT * FROM usermessage WHERE mid= ?';

//根据用户ID 查询信息
const queryisNull='SELECT * FROM userinfolist WHERE uid = ? ';

//根据用户ID联表查询
const queryLCU='SELECT uid,uname,upic FROM userinfolist WHERE uid IN (SELECT messgetter  FROM usermessage  WHERE messsender = ? ORDER BY sendtime DESC) limit 0 , 12 ';

//添加新用户
const inNewUser='INSERT INTO userinfolist (uid,uname,upassword,usex,uiden,usalt) VALUES ( ? , ? , ? , ? , ? , ? )';
//添加新消息
const inNewMessage='INSERT INTO usermessage (messtitle,messcontent,messsender,messgetter,sendtime) VALUES ( ? , ? , ? , ? , ? )';
//将草稿消息正式发送
const updataNewMessage='UPDATE usermessage SET messtitle = ? , messcontent = ? , messgetter = ? , sendtime = ? WHERE mid = ?';
//更改草稿消息
const updataSaveMessage='UPDATE usermessage SET messtitle = ? , messcontent = ? , messgetter = ?  WHERE mid = ?';
//更改除回收站的单条消息为已读（点击消息时自动标为已读）
const updataMessIsRead='UPDATE usermessage SET gettime = ? WHERE mid = ? AND  messstate != 2';
//更改消息为未读
//const updataMessIsUnRead='UPDATE usermessage SET gettime = NULL WHERE mid IN (?)';
//更改消息为收藏/取消收藏
const updteMessisStar='UPDATE usermessage SET messstate = IF(messstate=1,0,1)  WHERE mid = ?';

//根据用户ID 更改用户登录时间
const updateULLT='UPDATE userinfolist SET ulastlogintime = ?  WHERE uid = ?';
//根据用户ID 更改用户基本信息
const updateUserBaseInfo='UPDATE userinfolist SET uname = ? , usex = ? , uiden = ? , udesc = ? WHERE uid = ?';
//根据用户ID 更改用户密码
const updateUserPWDInfo='UPDATE userinfolist SET upassword = ? , usalt = ? WHERE uid = ?';
//根据用户ID 更改用户头像
const updateUserPicInfo='UPDATE userinfolist SET upic = ?  WHERE uid = ?';
//根据用户盐值 更改除密码外的信息
const updateUserAllInfo='UPDATE userinfolist SET uid = ? , uname = ? , usex = ? , uiden = ? , udesc = ? , upic = ?  WHERE usalt = ?';
//根据用户ID 封禁用户
const updateUserisLock='UPDATE userinfolist SET uiden = IF(uiden=5,1,5)  WHERE uid = ?';

//删除用户
const deleUser='DELETE FROM userinfolist WHERE uid = ? ';
//清除回收站一个月外的消息
const deleMess='DELETE FROM usermessage WHERE messgetter = ? AND messstate = 2 AND date_sub(curdate(), interval 1 month) > date(gettime)';

//4.预处理语句并执行
//返回忘记密码是否正确
function queryForgetPWD($con,$sql,$id,$name){
    $stmt = $con->prepare($sql);
    $stmt->bind_param('is',$id,$name);
    $stmt->execute();
    $res=$stmt->get_result();
    $rows=$res->fetch_row();
    return $rows;
}


//返回除登录者外的用户信息(分页)
function queryAllexLU($con,$sql,$id,$iden,$search,$pagenow){
    $stmt = $con->prepare($sql);
    $setBegin=3*($pagenow-1);
    $search="%".$search."%";
    $stmt->bind_param('iisi',$id,$iden,$search,$setBegin);
    $stmt->execute();
    $stmt->bind_result($uid,$uname,$upassword,$usex,$upic,$udesc,$uiden,$usalt,$ulastlogintime);
    $reslist=array();
    for($i=0;$stmt->fetch();++$i){
        $reslist[$i]=array($uid,$upic,$uname,$udesc,$usex,$uiden,$ulastlogintime,$usalt);
    }
    return $reslist;
}
//返回除登录者外的用户信息(记录数)
function queryAll($con,$sql,$id,$iden,$search){
    $stmt = $con->prepare($sql);
    $search="%".$search."%";
    $stmt->bind_param('iis',$id,$iden,$search);
    $stmt->execute();
    $stmt->store_result();
    return $stmt->num_rows;
}

//返回登录者的未读/回收站信息
function queryUndealMess($con,$sql,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param('i',$id);
    $stmt->execute();
    $stmt->bind_result($mid,$title,$content,$senderID,$geterID,$state,$sendtime,$gettime);
    $reslist=array();
    for($i=0;$stmt->fetch();++$i){
        $reslist[$i]=array($mid,$title,$content,$senderID,$geterID,$state,$sendtime,$gettime);
    }
    return $reslist;
}

//返回登录者的未读(页头专用)
function queryUnReadMess($con,$sql,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param('i',$id);
    $stmt->execute();
    $stmt->bind_result($mid,$title,$content,$senderID,$geterID,$state,$sendtime,$gettime,$sendName);
    $reslist=array();
    for($i=0;$stmt->fetch();++$i){
        $reslist[$i]=array($mid,$title,$content,$senderID,$geterID,$state,$sendtime,$gettime,$sendName);
    }
    return $reslist;
}

//返回登录者的 草稿/已发送 消息(分页)
function queryBrowse($con,$sql,$id,$search,$pagenow){
    $stmt = $con->prepare($sql);
    $setBegin=3*($pagenow-1);
    $search="%".$search."%";
    $stmt->bind_param('isi',$id,$search,$setBegin);
    $stmt->execute();
    $stmt->bind_result($mid,$title,$content,$senderID,$geterID,$state,$sendtime,$gettime,$geterORsendName);
    $reslist=array();
    for($i=0;$stmt->fetch();++$i){
        $reslist[$i]=array($mid,$title,$content,$senderID,$geterID,$state,$sendtime,$gettime,$geterORsendName);
    }
    return $reslist;
}
//返回登录者的 草稿/已发送 消息(记录数)
function queryBrowseNum($con,$sql,$id,$search){
    $stmt = $con->prepare($sql);
    $search="%".$search."%";
    $stmt->bind_param('is',$id,$search);
    $stmt->execute();
    $stmt->store_result();
    return $stmt->num_rows;
}

//返回特定用户信息
function queryInfoByID($con,$sql,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param('i',$id);
    $stmt->execute();
    $res=$stmt->get_result();
    $rows=$res->fetch_row();
    return $rows;
}

//返回登录者最近联系的10个用户的头像、姓名、ID
function queryLastConnectUserInfo($con,$sql,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param('i',$id);
    $stmt->execute();
    $stmt->bind_result($LCUID,$LCUName,$LCUPic);
    $reslist=array();
    for($i=0;$stmt->fetch();++$i){
        $reslist[$i]=array($LCUID,$LCUName,$LCUPic);
    }
    return $reslist;
}

//添加新用户
function inNewUser($con,$sql,$field1,$field2,$field3,$field4,$field5,$field6){
    $stmt = $con->prepare($sql);
    $stmt->bind_param("issiis",$field1,$field2,$field3,$field4,$field5,$field6);
    if($stmt->execute()) return true;
    return false;
}

//添加新信息&新信息存入草稿
function inNewMessage($con,$sql,$field1,$field2,$field3,$field4,$flag){
    $flag ? $nowdata=date('Y-m-d H:i:s', time()) : $nowdata=null;
    if (empty($field4)) $field4=null;
    $stmt = $con->prepare($sql);
    $stmt->bind_param("ssiis",$field1,$field2,$field3,$field4,$nowdata);
    if($stmt->execute()) return true;
    return false;
}

//将草稿正式发送
function upSaveToSend($con,$sql,$field1,$field2,$field3,$id){
    $nowdata=date('Y-m-d H:i:s', time());
    $stmt = $con->prepare($sql);
    $stmt->bind_param("ssisi",$field1,$field2,$field3,$nowdata,$id);
    if($stmt->execute()) return true;
    return false;
}

//更改草稿
function upSaveMess($con,$sql,$field1,$field2,$field3,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param("ssii",$field1,$field2,$field3,$id);
    if($stmt->execute()) return true;
    return false;
}

//更改消息为已读状态 更改消息为未读状态 将消息移动至回收站
function upMess($con,$sql){
    if($con->query($sql)) return true;
    return false;
}

//更改用户最后登陆时间
function upULLT($con,$sql,$id){
    $nowdata=date('Y-m-d H:i:s', time());
    $stmt = $con->prepare($sql);
    $stmt->bind_param("si",$nowdata,$id);
    if($stmt->execute()) return true;
    return false;
}

//更改用户基本信息
function upBaseInfo($con,$sql,$field1,$field2,$field3,$field4,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param("siisi",$field1,$field2,$field3,$field4,$id);
    if($stmt->execute()) return true;
    return false;
}

//更改用户密码
function upPWDInfo($con,$sql,$field1,$field2,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param("ssi",$field1,$field2,$id);
    if($stmt->execute()) return true;
    return false;
}

//更改用户头像
function upPicInfo($con,$sql,$field,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param("si",$field,$id);
    if($stmt->execute()) return true;
    return false;
}

//更改用户除密码外的所有信息
function upAllInfo($con,$sql,$field1,$field2,$field3,$field4,$field5,$field6,$key){
    $stmt = $con->prepare($sql);
    $stmt->bind_param("isiisss",$field1,$field2,$field3,$field4,$field5,$field6,$key);
    if($stmt->execute()) return true;
    return false;
}

//封禁/解封用户 收藏/取消收藏
function uplockUser($con,$sql,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param("i",$id);
    if($stmt->execute()) return true;
    return false;
}

//删除用户 删除一个月外的消息
function deleteUser($con,$sql,$id){
    $stmt = $con->prepare($sql);
    $stmt->bind_param("i",$id);
    if($stmt->execute()) return true;
    return false;
}